﻿CREATE PROCEDURE [dbo].[books_select_by_id]
    @bookId int
AS 
SELECT
    [BookId],
    [DateOfPublish],
    [ShortContent],
    [Title],
    [Avatar],
    [FileName],
    [UserId],
   (SELECT DISTINCT CAST(BooksByAuthor.AuthorId as nvarchar) + ',' AS 'data()'
                     FROM   BooksByAuthor
                     WHERE  BooksByAuthor.BookId = @bookId FOR xml path('')) AS authors,
   (SELECT DISTINCT CAST(BooksByCategory.CategoryId as nvarchar) + ',' AS 'data()'
                     FROM   BooksByCategory
                     WHERE  BooksByCategory.BookId = @bookId FOR xml path('')) AS categories
FROM [dbo].[Books] 
WHERE
    Books.[BookId]=@bookId
RETURN 0
